<?php
$root = $_SERVER['DOCUMENT_ROOT'] . "/php/";

include_once "$root/class/EggUtil.php";

class SqlManager
{
    /**
     * Sql连接实例
     * @var PDO
     */
    private $sqlConnect;

    /**
     * Sql表名
     * @var string
     */
    private $table;

    /**
     * 中间字符
     * @var string
     */
    private $middle = "=";

    /**
     * 尾部字符
     * @var string
     */
    private $sqlExtend = ",";

    /**
     * Sql旁边的字符
     * @var string
     */
    private $sqlBoth = "";

    /**
     * Sql插入模板
     * @var string
     */
    private $insertSql = "";

    /**
     * Sql删除模板
     * @var string
     */
    private $deleteSql = "";

    /**
     * Sql查询模板
     * @var string
     */
    private $querySql = "";

    /**
     * Sql修改模板
     * @var string
     */
    private $alterSql = "";

    private $showTableSql = "";

    public function __construct($table)
    {
        $this->table = $table;
        $this->insertSql = "INSERT INTO `$table` ";
        $this->deleteSql = "DELETE FROM `$table` WHERE ";
        $this->querySql = "SELECT * FROM `$table` WHERE ";
        $this->alterSql = "UPDATE `$table` SET ";
        $this->showTableSql = "SELECT * FROM `$table`";
    }

    /**
     * 设置连接实例
     * @param PDO $sqlConnect
     */
    public function setSqlConnect(PDO $sqlConnect)
    {
        $this->sqlConnect = $sqlConnect;
    }

    /**
     * 获取连接实例
     * @return PDO
     */
    public function getSqlConnect()
    {
        return $this->sqlConnect;
    }

    /**
     * 添加数据
     * @param $arr
     * @return bool
     */
    public function addFieldData($arr)
    {
        try {
            $this->sqlBoth = "";
            $stat = $this->sqlPrepare($this->insertSql . $this->makeInsertTemplate(array_keys($arr)));
            $this->bindValue($stat, $arr);

            if (!$stat->execute()) {
                print_r($stat->errorInfo());
            } else {
                return true;
            }
        } catch (PDOException $e) {
            print_r($e);
            return false;
        }
        return false;
    }

    /**
     * 删除数据
     * @param $where
     * @param bool $isUseId
     * @param bool $isIndex
     * @return bool
     */
    public function deleteField($where, $isUseId = true, $isIndex = false)
    {
        try {
            $arr = $this->checkUseId($isUseId, $where, $isIndex);
            $this->middle = "=";
            $stat = $this->sqlPrepare($this->deleteSql . $this->makeParamsTemplate(array_keys($arr)));
            $this->bindValue($stat, $arr);
            if (!$stat->execute()) {
                print_r($stat->errorInfo());
            } else {
                return $stat->rowCount();
            }
        } catch (PDOException $e) {
            echo $e;
            return false;
        }
        return false;
    }

    /**
     * 查询数据
     * @param $where
     * @param bool $isUseId
     * @param bool $isIndex
     * @return array|bool
     */
    public function queryFieldData($where, $isUseId = true, $isIndex = false)
    {
        try {
            $arr = $this->checkUseId($isUseId, $where, $isIndex);
            $this->sqlExtend = " OR ";
            $stat = $this->sqlPrepare($this->querySql . $this->makeParamsTemplate(array_keys($arr)));

            $this->bindValue($stat, $arr);
            if (!$stat->execute()) {
                print_r($stat->errorInfo());
                return false;
            } else {
                return $stat->fetchAll();
            }
        } catch (PDOException $e) {
            echo $e;
            return false;
        }
    }

    /**
     * 修改数据
     * @param $where
     * @param $valArr
     * @param bool $isUseId
     * @param bool $isIndex
     * @return bool
     */
    public function alterData($where, $valArr, $isUseId = true,$isIndex = false)
    {
        try {
            $arr = $this->checkUseId($isUseId, $where,$isIndex);
            $this->sqlBoth = "";
            $template = $this->makeParamsTemplate(array_keys($valArr)) . " WHERE " . $this->makeParamsTemplate(array_keys($arr));
            $stat = $this->sqlPrepare($this->alterSql . $template);
            $this->bindValue($stat, $arr);
            $this->bindValue($stat, $valArr);
            if (!$stat->execute()) {
                print_r($stat->errorInfo());
            } else {
                return true;
            }
        } catch (PDOException $e) {
            echo $e;
            return false;
        }
        return false;
    }

    public function getTableData()
    {
        return $this->sqlConnect->query($this->showTableSql)->fetchAll();
    }

    /**
     * 检查是否用Id当作查询条件
     * @param $isUseId
     * @param $where
     * @param bool $isIndex
     * @return array
     */
    private function checkUseId($isUseId, $where, $isIndex = false)
    {
        $arr = array();
        if ($isUseId) {
            $arr["Id"] = $where;
            $this->middle = " = ";
            $this->sqlBoth = "";
        } else {
            $arr = $where;
            $this->middle = " LIKE ";
            $this->sqlBoth = "%";
        }
        if ($isIndex) {
            $this->sqlBoth = "";
        }
        return $arr;
    }

    /**
     * 预处理数据
     * @param string $sqlTemplate
     * @return PDOStatement
     */
    private function sqlPrepare(string $sqlTemplate): PDOStatement
    {
        return $this->sqlConnect->prepare($sqlTemplate);
    }

    /**
     * 删除尾部部分字符
     * @param  $str
     * @param $targetStr
     */
    private function removeEndStr(&$str, $targetStr = 1)
    {
        $str = substr($str, 0, strlen($str) - strlen($targetStr));
    }

    /**
     * 制作插入预处理模板
     * @param $arr
     * @return string
     */
    private function makeParamsTemplate($arr)
    {
        $sql = "";
        foreach ($arr as $k) {
            $sql .= "`$k`$this->middle:$k$this->sqlExtend";
        }
        $this->removeEndStr($sql, $this->sqlExtend);
        return $sql;
    }


    /*private function makeBothParamsTemplate($arr)
    {
        $sql = "";
        $arrSize = count($arr);
        foreach ($arr as $k => $v) {
            $extend = "OR";
            if (($arrSize - 1) == $k) {
                $extend = "";
            }
            $sql .= $this->makeParamsTemplate(array_keys($v)) . $extend;
        }
    }*/

    /**
     * 制作预处理模板
     * @param $arr
     * @return string
     */
    private function makeInsertTemplate($arr)
    {
        $keys = "";
        $placeholder = "";
        foreach ($arr as $key) {
            $keys .= "`$key`,";
            $placeholder .= ":$key,";
        }
        $this->removeEndStr($keys);
        $this->removeEndStr($placeholder);
        return "($keys) VALUES ($placeholder)";
    }

    /**
     * 绑定数据
     * @param PDOStatement $stmt
     * @param $arr
     */
    private function bindValue(PDOStatement $stmt, $arr)
    {
        foreach ($arr as $k => $v) {
            $value = "$this->sqlBoth$v$this->sqlBoth";
            $stmt->bindValue(":$k", $value);
        }
    }
}